﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ES_Project.Model;
using MySql.Data.MySqlClient;
using System.Data;
using System.Web.Configuration;
using System.Threading;
using System.Globalization;

namespace ES_Project.Pages
{
    public partial class WebForm33 : System.Web.UI.Page
    {
        string StrConn = WebConfigurationManager.ConnectionStrings["baseessystem"].ToString();
        MySqlCommand objCmd = new MySqlCommand(); 
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //CMD_INSERT.Enabled = true;
                //CMD_EDIT.Enabled = false;
            }
        }

        protected void CMD_INSERT_Click(object sender, EventArgs e)
        {
             using (essystemEntities context = new essystemEntities())
             {
                Button button = (Button)sender;
                switch (button.ID)
                {
                    case "CMD_INSERT":
                        emp_mast empMast = new emp_mast();
                        empMast.emp_code = empCode.Text;
                        empMast.emp_name = empName.Text;
                        //empMast.emp_nickname = empNickname.Text;
                        //empMast.emp_degree = empDrgree.Text;
                        //empMast.emp_degree_subject = empDegreeSubject.Text;
                        //empMast.emp_institution = empInstitution.Text;
                        //if (empBirthday.Text != null && empBirthday.Text != "")
                        //{
                        //    empMast.emp_birthday = Convert.ToDateTime(empBirthday.Text);
                        //}
                        if (empAge.Text != null && empAge.Text != "")
                        {
                            empMast.emp_age = Convert.ToInt32(empAge.Text);
                        }
                        //empMast.emp_gender = empGender.Text;
                        //empMast.emp_id_card = empIdCard.Text;
                        //empMast.emp_address = empAddress.Text;
                        //empMast.emp_phone = empPhone.Text;
                        //empMast.emp_account_no = empAccountNo.Text;
                        //empMast.emp_bank_name = empBankName.Text;
                        //empMast.emp_bank_branch = empBankBranch.Text;
                        //empMast.emp_tax_no = empTaxNo.Text;
                        //empMast.emp_tax_invoice = empTaxInvoice.Text;
                        //empMast.emp_email = empEmail.Text;
                        if (empBegin.Text != null && empBegin.Text != "")
                        {
                            empMast.emp_begin = Convert.ToDateTime(empBegin.Text);
                        }
                        if (empEnd.Text != null && empEnd.Text != "")
                        {
                            empMast.emp_end = Convert.ToDateTime(empEnd.Text);
                        }
                        //empMast.emp_quit = empQuit.Text;
                        context.emp_mast.AddObject(empMast);
                        if (context.SaveChanges() > 0)
                        {

                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert1", "alert('บันทึกเรียบร้อยแล้ว')", true);
                            GridView4.DataBind();
                            GridView4.SelectedIndex = -1;
                            clear();
                        }
                        break;
                    case "CMD_EDIT":
                         List<emp_mast> empMS = context.emp_mast.Where("it.[emp_id]=" + emp_id.Text + " ").ToList();
                         if (empMS.Count() == 1)
                         {
                             emp_mast empMEdit = context.emp_mast.Where("it.[emp_id]=" + emp_id.Text + "").First();
                             if (empMEdit != null) {
                                 empMEdit.emp_code = empCode.Text;
                                 empMEdit.emp_name = empName.Text;
                                 //empMEdit.emp_nickname = empNickname.Text;
                                 //empMEdit.emp_degree = empDrgree.Text;
                                 //empMEdit.emp_degree_subject = empDegreeSubject.Text;
                                 //empMEdit.emp_institution = empInstitution.Text;
                                 //empMEdit.emp_birthday = Convert.ToDateTime(empBirthday);
                                 empMEdit.emp_age = Convert.ToInt32(empAge.Text);
                                 //empMEdit.emp_gender = empGender.Text;
                                 //empMEdit.emp_id_card = empIdCard.Text;
                                 //empMEdit.emp_address = empAddress.Text;
                                 //empMEdit.emp_phone = empPhone.Text;
                                 //empMEdit.emp_account_no = empAccountNo.Text;
                                 //empMEdit.emp_bank_name = empBankName.Text;
                                 //empMEdit.emp_bank_branch = empBankBranch.Text;
                                 //empMEdit.emp_tax_no = empTaxNo.Text;
                                 //empMEdit.emp_tax_invoice = empTaxInvoice.Text;
                                 //empMEdit.emp_email = empEmail.Text;
                                 empMEdit.emp_begin = Convert.ToDateTime(empBegin);
                                 empMEdit.emp_end = Convert.ToDateTime(empEnd);
                                 //empMEdit.emp_quit = empQuit.Text;

                                 if (context.SaveChanges() > 0)
                                 {

                                     ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
                                     GridView4.DataBind();
                                     GridView4.SelectedIndex = -1;
                                     //CMD_INSERT.Enabled = true;
                                     //CMD_EDIT.Enabled = false;
                                     clear();

                                 }
                             }
                         }
                        break;
                }

            }
             
        }
        protected void GridView4_SelectedIndexChanged(object sender, EventArgs e)
        { 
            //CMD_INSERT.Enabled = false;
            //CMD_EDIT.Enabled = true;

            using (essystemEntities context = new essystemEntities())
            {

                List<emp_mast> mao = context.emp_mast.Where("it.[emp_id]=" + ((HiddenField)GridView4.SelectedRow.Cells[1].FindControl("HiddenField3")).Value.ToString() + "").ToList();
                if (mao.Count > 0)
                {
                    List<emp_mast> mb = context.emp_mast.Where("it.[emp_id]=" + ((HiddenField)GridView4.SelectedRow.Cells[1].FindControl("HiddenField3")).Value.ToString() + "").ToList();
                    if (mb.Count > 0)
                    {
                        empCode.Text = mb[0].emp_code;
                        empName.Text = mb[0].emp_name;
                        //empNickname.Text = mb[0].emp_nickname;
                        //empDrgree.Text = mb[0].emp_gender;
                        emp_id.Text = mb[0].emp_id.ToString();
                        //empDegreeSubject.Text = mb[0].emp_degree_subject;
                        //empInstitution.Text = mb[0].emp_institution;
                        //empBirthday.Text = Convert.ToString(mb[0].emp_birthday);
                        empAge.Text = Convert.ToString(mb[0].emp_age);
                        //empGender.Text = mb[0].emp_gender;
                        empIdCard.Text = mb[0].emp_id_card;
                        //empAddress.Text = mb[0].emp_address;
                        //empPhone.Text = mb[0].emp_phone;
                        //empAccountNo.Text = mb[0].emp_account_no;
                        //empBankName.Text = mb[0].emp_bank_name;
                        //empBankBranch.Text = mb[0].emp_bank_branch;
                        //empTaxNo.Text = mb[0].emp_tax_no;
                        //empTaxInvoice.Text = mb[0].emp_tax_invoice;
                        //empEmail.Text = mb[0].emp_email;
                        empBegin.Text = Convert.ToString(mb[0].emp_begin);
                        empEnd.Text = Convert.ToString(mb[0].emp_end);
                        //empQuit.Text = mb[0].emp_quit;
                        
                    }
                }
                 
            }

        }
        protected void GridView4_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {

                ImageButton ib = (ImageButton)e.Row.FindControl("ImageButton1");
                ib.Attributes.Add("onclick", "return confirm('คุณต้องการที่จะลบข้อมูล?');");

            }
            HyperLink HyFO4 = (HyperLink)(e.Row.FindControl("HyFO4"));
            if (HyFO4 != null)
            {
                // HyFO4.Text = (string)DataBinder.Eval(e.Row.DataItem, "AgentCode");
                //HyFO4.NavigateUrl = "Pay_Course.aspx?emp_id=" + (string)DataBinder.Eval(e.Row.DataItem, "emp_id").ToString();
                HyFO4.NavigateUrl = "#";
                HyFO4.Attributes.Add("Onclick", "opennewsletter('Pay_Course.aspx?emp_id=" + (string)DataBinder.Eval(e.Row.DataItem, "emp_id").ToString() + "');");
                //+ "&TxtAO=" + TxtAO.Text + "&TxtAgent=" + TxtAgent.Text + "&TxtInvoice1=" + TxtInvoice1.Text + "&TxtInvoice2=" + TxtInvoice2.Text + "&TxtDate1=" + TxtDate1.Text + "&TxtDate2=" + TxtDate2.Text + "&StatusWork=" + StatusWork.SelectedValue;
            }
            HyperLink edit = (HyperLink)(e.Row.FindControl("edit"));
            if (edit != null)
            {
                // HyFO4.Text = (string)DataBinder.Eval(e.Row.DataItem, "AgentCode");
                edit.NavigateUrl = "Teacher.aspx?emp_id=" + (string)DataBinder.Eval(e.Row.DataItem, "emp_id").ToString();
                //HyFO4.NavigateUrl = "#";
                //HyFO4.Attributes.Add("Onclick", "opennewsletter('Pay_Course.aspx?emp_id=" + (string)DataBinder.Eval(e.Row.DataItem, "emp_id").ToString() + "');");
                //+ "&TxtAO=" + TxtAO.Text + "&TxtAgent=" + TxtAgent.Text + "&TxtInvoice1=" + TxtInvoice1.Text + "&TxtInvoice2=" + TxtInvoice2.Text + "&TxtDate1=" + TxtDate1.Text + "&TxtDate2=" + TxtDate2.Text + "&StatusWork=" + StatusWork.SelectedValue;
            }
        }
        //private void GridView4_RowDataBound(object sender, GridViewRowEventArgs e)
        //{
        //    //*** hplHyperLink ***'
        //    HyperLink hplHyperLink = (HyperLink)e.Row.FindControl("HyFO4");
        //    if ((hplHyperLink != null))
        //    {
        //        hplHyperLink.NavigateUrl = "#";
        //        hplHyperLink.Attributes.Add("Onclick", "javascript::openW('Pay_Course.aspx?emp_id=" + (string)DataBinder.Eval(e.Row.DataItem, "emp_id").ToString() + "');");
        //    }
        //}
        private void clear()
        {
            empCode.Text = "";
            empName.Text = "";
            empName.Text = "";
            empBegin.Text = "";
            empEnd.Text = "";
            //empBirthday.Text = "";
            //empAccountNo.Text = "";
            //empAddress.Text = "";
            empAge.Text = "";
            //empBankBranch.Text = "";
            //empBankName.Text = "";
            //empDegreeSubject.Text = "";
            //empEmail.Text = "";
            empIdCard.Text = "";
            //empPhone.Text = "";
            //empGender.Text = "";
            //empInstitution.Text = "";
            //empTaxInvoice.Text = "";
            //empTaxNo.Text = "";
            //empQuit.Text = "";
        }

        protected void CMD_CLEAR_Click(object sender, EventArgs e)
        {
            clear();
        }

        protected void CMD_SEARCH_Click(object sender, EventArgs e)
        {
            
            //TextBox1.Text = toDate(empBegin.Text);
            //TextBox2.Text = toDate(empEnd.Text);
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;
            String condi = " where 1=1 ";

            if (!empCode.Text.Equals(""))
            {
                condi = condi + " and em.emp_code = '" + empCode.Text + "'";

            }
            if (!empName.Text.Equals(""))
            {
                condi = condi + " and em.emp_name = '" + empName.Text + "'";

            }
            if (!empBegin.Text.Equals(""))
            {
                //condi = condi + " and em.emp_begin = '" +String.Format("{0:yyyy-MM-dd HH:mm:ss}", Convert.ToDateTime(empBegin.Text)) + "' ";
                condi = condi + " and em.emp_begin = '" + toDate(empBegin.Text) + "'";
                //condi = condi + " and em.emp_begin = concat(substring(STR_TO_DATE(" + empBegin.Text + ",'%d/%m/%Y'),1,4)-543,substring(STR_TO_DATE(" + empBegin.Text + ",'%d/%m/%Y'),5,8)) ";
            }
            if (!empEnd.Text.Equals(""))
            {
                condi = condi + " and em.emp_end = '" +toDate(empEnd.Text) + "'";

            }
            if (!empAge.Text.Equals(""))
            {
                condi = condi + " and em.emp_age = '" + empAge.Text + "'";

            }
            if (!empIdCard.Text.Equals(""))
            {
                condi = condi + " and em.emp_id_card = '" + empIdCard.Text + "'";

            }
            

            strSQL = "select emp_id, emp_code, emp_name, pay_id, emp_quit, emp_begin, "+ 
                     "emp_end, emp_tax_invoice, emp_email, emp_bank_branch, emp_tax_no, "+
                     "emp_account_no, emp_phone, emp_bank_name, emp_address, emp_id_card, "+
                     "emp_gender, emp_age, emp_birthday, emp_institution, emp_degree_subject, "+ 
                     "emp_degree, emp_nickname "+
                     "from emp_mast em "+condi+" ";




            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds = new DataSet();
            da4.Fill(ds, "emp_mast");

            if (ds.Tables[0].Rows.Count > 0)
            {
                GridView4.DataSource = ds.Tables["emp_mast"];
                GridView4.DataBind();

            }
            else
            {
                GridView4.DataSource = null;
                GridView4.DataBind();
                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert1", "alert('ไม่พบข้อมูล')", true);
            }
        }
        //dd/MM/YYYY(TH) --> yyyy/MM/dd(TH)
        public static String toDate(String date)
        {
            String day = date.Split('/')[0];
            String month = date.Split('/')[1];
            String year = (Convert.ToInt32(date.Split('/')[2])-543).ToString();
            
            //DateTime dateForMat = DateTime.Parse((day + "/" + month + "/" + year));
            //String dateUs = string.Format("{0:yyyy/MM/dd HH:mm:ss}", dateForMat);
            //return dateUs;
            return string.Format("{0:yyyy/MM/dd HH:mm:ss}", DateTime.Parse((day + "/" + month + "/" + year)));
        }

    }
}